﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using Excel;
using System.Data.Odbc;
using System.Data.OleDb;
using OfficeOpenXml;

namespace OrderAggregator.InputFormats.Excel
{
	/// <summary>
	/// Базовый формат для Excel.
	/// </summary>
	public abstract class BaseExcelFormat : BaseOrderFormat
	{
		/// <summary>
		/// Read as dataset
		/// </summary>
		/// <param name="stream"></param>
		/// <returns></returns>
		protected DataSet ReadAsDataset(Stream stream)
		{
            try
            {
                using (var excelReader = ExcelReaderFactory.CreateBinaryReader(stream))
                {
                    return excelReader.AsDataSet();
                }
            }
            catch
            {
                return null;
            }
		}

        /// <summary>
        /// Read XLSX file as dataTable
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        protected IDisposable ReadXLSXAsDataTable(string filePath, out Func<int, Func<int, object>> getRow)
        {
            //todo
            using (var stream = File.OpenRead(filePath))
            {
                var package = new ExcelPackage(stream);

                var sheet = package.Workbook.Worksheets[1];

                var dim = sheet.Dimension;

                getRow = (r) =>
                             {
                                 if (r >= dim.End.Row)
                                 {
                                     return null;
                                 }

                                 return (c) => sheet.GetValue(r + 1, c + 1);
                             };

                return package;

            }
        }

        /// <summary>
        /// Read as dataTable
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        protected DataTable ReadAsDataTable(string filePath)
        {

            var workSheetNumber = 0;

            var strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1'";
            
            using (var conn = new OleDbConnection(strConn))
            {
                conn.Open();
                
                var schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[] { null, null, null, "TABLE" });
                var SpreadSheetName = "[" + schemaTable.Rows[workSheetNumber]["TABLE_NAME"].ToString() + "]";

                var cmd = new OleDbCommand(@"SELECT * FROM " + SpreadSheetName, conn);
                
                var table = new DataTable();

                var da = new OleDbDataAdapter(cmd);
                
                da.Fill(table);

                return table;
            }

        }
        
	}
}
